import uuid

import os

import pandas as pd
from xpinyin import Pinyin

from mapper.menumapper import MenuMapper
from utils.dbutils import *
from mapper.deptmapper import *
from mapper.rolemapper import *
from mapper.positionmapper import *
from mapper.usermapper import *
from mapper.humanresmapper import *

p = Pinyin()

mysql_tools = MysqlTools(db_config=DB_Config.get("locatest"))

dept_index = 2
role_index = 2


def file_name(file_dir):
    for root, dirs, files in os.walk(file_dir):
        return [root + '/' + file for file in files if ".xlsx" in file]


def get_dept(dept_name):
    global dept_index
    dept = {
        "dept": dept_name
    }
    sql = DeptMapper.GET_DEPT.format(**dept)
    dept_id = mysql_tools.select_one(sql)
    if not dept_id is None:
        return dept_id.get("dept_id")
    else:
        dept["dept_id"] = dept_index
        dept_index += 1
        sql = DeptMapper.ADD_DEPT.format(**dept)
        dept_id = mysql_tools.execute_sql(sql, commit=True)
        return get_dept(dept_name)


def get_role(role_name):
    global role_index
    role = {
        "role": role_name
    }
    sql = RoleMapper.GET_ROLE.format(**role)
    role_id = mysql_tools.select_one(sql)
    if not role_id is None:
        return role_id.get("role_id")
    else:
        role_index+=1
        
        sql = RoleMapper.ADD_ROLE.format(**role)
        dept_id = mysql_tools.execute_sql(sql, commit=True)
        # 添加列表展示
        return get_role(role_name)


def get_position(position_name):
    position = {
        "position": position_name
    }
    sql = PostitionMapper.GET_POSITION.format(**position)
    position_id = mysql_tools.select_one(sql)
    if not position_id is None:
        return position_id.get("position_id")
    else:
        sql = PostitionMapper.ADD_POSITION.format(**position)
        dept_id = mysql_tools.execute_sql(sql, commit=True)
        return get_position(position_name)


def get_age(age: str):
    age = int(age)
    if age <= 25:
        return '18-25'
    for i in range(5):
        start = 25 + i * 5
        end = 25 + i * 5 + 5
        if age > start and age <= end:
            return "{}-{}".format(start, end)
    else:
        return "50以上"


def get_household_classify(domicile, household_classify):
    local_str = "本地"
    if not "西安" in domicile:
        local_str = "外地"
    
    if "农" in household_classify:
        return local_str + "农村"
    else:
        return local_str + "城镇"


def file_load(file):
    d = pd.read_excel(file)
    data = pd.DataFrame(d)
    for i in range(len(data)):
        dict = {
            "user_name": str(p.get_pinyin(data.iloc[i][4])).replace("-", "") + str(uuid.uuid4())[:6],
            "password": "123456",
            "role_id": get_role(data.iloc[i]["系统角色"]),
            "jobnumber": data.iloc[i]["工号"],
            "real_name": data.iloc[i]["姓名"],
            "user_tel": data.iloc[i]["手机"],
            "dept_id": get_dept(data.iloc[i]["部门"]),
            "position_id": get_position(data.iloc[i]["职位"]),
            "sex": data.iloc[i]["性别"],
            "age": get_age(data.iloc[i]["年龄"]),
            "height": data.iloc[i]["身高"] if data.iloc[i]["身高"] > 10 else data.iloc[i]["身高"] * 100,
            "weight": data.iloc[i]["体重"],
            "nation": data.iloc[i]["民族"] + "族",
            "political_status": data.iloc[i]["政治面貌"],
            "is_marry": data.iloc[i]["婚姻状况"],
            "legal_record": data.iloc[i]["违法记录"] == "有",
            "have_contagion": data.iloc[i]["传染病史"] == "有",
            "have_chronic": data.iloc[i]["慢性病史"] == "有",
            "join_ncms": data.iloc[i]["是否上新农合"] == "是",
            "join_army": data.iloc[i]["退伍军人"] == "是",
            "user_school": data.iloc[i]["毕业院校"],
            "profession": data.iloc[i]["所学专业"],
            "user_edu": data.iloc[i]["学历"],
            "certificate": data.iloc[i]["所获得证书"],
            "household_classify": get_household_classify(data.iloc[i]["户籍地址"], data.iloc[i]["户口类型"]),
            "domicile": data.iloc[i]["户籍地址"],
            "address": data.iloc[i]["现居住地址"],
            "user_idcard": data.iloc[i]["身份证号"],
            "family_name1": data.iloc[i]["家庭成员姓名及关系1"],
            "family_tel1": data.iloc[i]["电话1"],
            "family_name2": data.iloc[i]["家庭成员姓名及关系2"],
            "family_tel2": data.iloc[i]["电话2"],
            "job_time": data.iloc[i]["入职日期"],
            "regular_time": data.iloc[i]["转正日期"],
            "pinyin": str(p.get_pinyin(data.iloc[i][4])).replace("-", "").title(),
            "status": 35
        }
        print(dict)
        
        user_sql = UserMapper.ADD_USER.format(**dict)
        print("ADD_USER:", user_sql)
        mysql_tools.execute_sql(user_sql, commit=True)
        get_user_sql = UserMapper.GET_BY_USER_NAME.format(**dict)
        print("GET_BY_USER_NAME:", get_user_sql)
        user_id = mysql_tools.select_one(get_user_sql)
        if not user_id is None:
            dict["user_id"] = user_id.get("user_id")
            humanres_sql = HuamnresMapper.ADD_HUMANRES.format(**dict)
            print("ADD_HUMANRES:", humanres_sql)
            mysql_tools.execute_sql(humanres_sql, commit=True)
    return data


def main():
    role_list = mysql_tools.select_all(RoleMapper.ROLE_ID_LIST)
    menu_list = mysql_tools.select_all(MenuMapper.MENU_LIST)
    
    for role in role_list:
        if role.get("role_id") == 1:
            continue
        params = {
            "role_id": role.get("role_id")
        }
        for menu in menu_list:
            params["menu_id"] = menu.get("menu_id")
            sql = RoleMapper.INSERT_ROLE_LIST.format(**params)
            print(sql)
            mysql_tools.execute_sql(RoleMapper.INSERT_ROLE_LIST, True)


if __name__ == '__main__':
    # files = file_name("./files")
    # for file in files:
    #     data = file_load(file)
    main()
